package com.niit.bookmanage.service.impl;

import com.niit.bookmanage.Utils.AbnormalUtils;
import com.niit.bookmanage.domain.vo.BookBorrowCountVO;
import com.niit.bookmanage.domain.vo.BookTypeVO;
import com.niit.bookmanage.domain.vo.BorrowVO;
import com.niit.bookmanage.hive.PlatformDictionary;
import com.niit.bookmanage.service.BookBorrowCountService;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

/**
 * @author Liweijian.
 * @date 2024/6/8 12:57
 */

@Service
public class BookBorrowCountImpl implements BookBorrowCountService {

    private static Connection conn = null;

    /*
    * task 1&2
    * */
    public int getCount(String statue) throws SQLException {
        int count = 0;
        try {
            init();
            String sql =  "SELECT COUNT(*) AS count\n" +
                          "FROM library2\n" +
                          "WHERE book_statue = ? " ;
            System.out.println("Running: " + sql);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, statue);
            ResultSet rs = pstmt.executeQuery();  // 执行查询并获取结果集
            if (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        } finally {
            destroy();
        }
        //System.out.println(sex +":"+ count);
        return count;
    }

    public int getCountByName(String bookName, String statue) {
        int count = 0;
        System.out.println(bookName);
        System.out.println(statue);
        try {
            init();
            String sql = "SELECT COUNT(*) AS count FROM library2 WHERE book_statue = ? AND book_name = ? " ;
            System.out.println("Running: " + sql);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, statue);
            pstmt.setString(2, bookName);
            ResultSet rs = pstmt.executeQuery();  // 执行查询并获取结果集
            if (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        } finally {
            destroy();
        }
        return count;
    }

    @Override
    public List<BorrowVO> getBorrowAndReturnCount(String bookName) {
        int count1;
        int count2;
        ArrayList<BorrowVO> list = new ArrayList<>();
        if(bookName==null) {
            try {
                count1 = getCount("已借");
                count2 = getCount("已还");
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        } else {
            count1 = getCountByName(bookName,"已借");
            count2 = getCountByName(bookName,"已还");
        }
        BorrowVO vo = new BorrowVO("borrowed", count1);
        BorrowVO vo1 = new BorrowVO("returned", count2);
        list.add(vo1);
        list.add(vo);
        return list;
    }

    /*
    * task 6
    * */
    @Override
    public List<BookBorrowCountVO> getTopFiveBooks() throws SQLException {
        List<BookBorrowCountVO> bookBorrowCountVOS = new ArrayList<>();
            init();
            String sql = "SELECT book_name, COUNT(*) AS keeped_count\n" +
                         "FROM library2\n" +
                         "GROUP BY book_name " ;
            System.out.println("Running: " + sql);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();  // 执行查询并获取结果集
            if (rs.next()) {
                while (rs.next()) {
                    String bookName = rs.getString("book_name");
                    int borrowedCount = rs.getInt("keeped_count");
                    BookBorrowCountVO vo = new BookBorrowCountVO(bookName, borrowedCount);
                    bookBorrowCountVOS.add(vo);
                }
            }


        //取count最大的五个值
        // 先根据借阅数量排序
        Collections.sort(bookBorrowCountVOS, Comparator.comparingInt(BookBorrowCountVO::getCount).reversed());
        // 取前五大的数
        List<BookBorrowCountVO> topFive = new ArrayList<>();
        for (int i = 0; i < Math.min(5, bookBorrowCountVOS.size()); i++) {
            topFive.add(bookBorrowCountVOS.get(i));
        }
        return topFive;
    }

    /*
    * task 5
    * */
    @Override
    public List<BookTypeVO> getTopThreeType() {
        List<BookTypeVO> bookTypeVOS = new ArrayList<>();
        try {
            init();
            String sql = "SELECT book_type, COUNT(*) AS borrowed_count\n" +
                         "FROM library2\n" +
                         "WHERE book_statue = '已借'\n" +
                         "GROUP BY book_type " ;
            System.out.println("Running: " + sql);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();  // 执行查询并获取结果集
            if (rs.next()) {
                while (rs.next()) {
                    String bookType = rs.getString("book_type");
                    int borrowedCount = rs.getInt("borrowed_count");
                    BookTypeVO vo = new BookTypeVO(bookType, borrowedCount);
                    bookTypeVOS.add(vo);
                }
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        } finally {
            destroy();
        }

        //取count最大的3个值
        // 先根据借阅数量排序
        bookTypeVOS.sort(Comparator.comparingInt(BookTypeVO::getCount).reversed());
        // 取前五大的数
        List<BookTypeVO> topThree = new ArrayList<>();
        for (int i = 0; i < Math.min(3, bookTypeVOS.size()); i++) {
            topThree.add(bookTypeVOS.get(i));
        }
        return topThree;
    }

    /*
    * 基础
    * */
    private static void init() {
        try {
            Class.forName(PlatformDictionary.DRIVER_NAME);
            conn = DriverManager.getConnection(PlatformDictionary.URL, PlatformDictionary.USER_NAME,
                    PlatformDictionary.PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        }
    }

    /**
     * 释放资源
     */
    private static void destroy() {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        }
    }

    @Override
    public List<String> getAllBookNames() {
        List<String> names = new ArrayList<>();
        names.add("三体");
        names.add("从头开始学音乐");
        names.add("史记");
        names.add("存在与时间");
        names.add("戏剧艺术");
        names.add("斗罗大陆");
        names.add("机器人系列");
        names.add("校花的贴身高手");
        names.add("论语");
        names.add("资治通鉴");
        return names;
    }
}
